/*******************************************************************************
 * This file is part of blueprint4j www.blueprint4j.org.
 * 
 *     blueprint4j is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU Lesser General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     blueprint4j is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU Lesser General Public License for more details.
 * 
 *     You should have received a copy of the GNU Lesser General Public License
 *     along with blueprint4j. If not, see <http://www.gnu.org/licenses/>.
 ******************************************************************************/
package blueprint4j.db;

import java.sql.*;
import java.util.Date;

import blueprint4j.utils.*;

import java.io.*;
import java.util.zip.*;
import java.text.SimpleDateFormat;

public class DataBaseUtilsDerby extends DataBaseUtils {

	private java.text.SimpleDateFormat simple_date_format = new java.text.SimpleDateFormat( "yyyy-MM-dd" );
	private java.text.SimpleDateFormat simple_time_format = new java.text.SimpleDateFormat( "HH:mm:ss" );
	private java.text.SimpleDateFormat simple_date_and_time_format = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );

	/**
	 * This method reads the next index field from the index field table.
	 */
	private final static long ID_NUMBER_GAP=100;
	private static long next_id_number=0;
	private static long next_id_block=0;

	public SimpleDateFormat getDateTimeFormat() {
		return simple_date_and_time_format;
	}

	public SimpleDateFormat getTimeFormat() {
		return simple_time_format;
	}

	public SimpleDateFormat getDateFormat() {
		return simple_date_format;
	}

	public Long getNextIndexField(Entity entity)
	throws SQLException {
		if (next_id_number==next_id_block) {
			Statement statement = DBTools.getLocalConnection(entity.getConnection()).createStatement();
			statement.execute("insert into " + IndexTable.TABLE_NAME + "(tablename) values ('" + entity.getTableName() + "')");
			ResultSet result_set = statement.executeQuery("select LAST_INSERT_ID()");
			if (!result_set.next()) {
				throw new SQLException("Could not read the next inserted index field");
			}
			next_id_number = result_set.getLong(1)*ID_NUMBER_GAP;
			next_id_block=next_id_number+ID_NUMBER_GAP;
			result_set.close();
			statement.close();
		}
		return new Long(next_id_number++);
	}

	public void loadIdAfterInsert(DBConnection connection,FieldId id)
	throws SQLException {
		Statement smt = connection.createStatement();
		try {
			ResultSet rs = smt.executeQuery("select IDENTITY_VAL_LOCAL() from " + id.getEntity().getTableName());
			rs.next();
			id.setAsString(rs.getString(1));
		}
		finally {
			smt.close();
		}
	}

	public void createIndexTable(Statement st)
	throws SQLException {
		st.execute("create table d_index_table(index_field bigint GENERATED ALWAYS AS IDENTITY,tablename varchar(64) not null,primary key(index_field))");
	}

	/**
	* Convert a Date to SQL leave out the time
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_format.format(date) + "'";
	}

	/**
	* Convert a Time to SQL leave out the date
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_time_format.format(date) + "'";
	}
	
	public String castTime(String date) {
		return "cast("+date+" AS TIME)";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value without time
	*/
	public String currentDateSQLMethod() {
		return "CURRENT_DATE()";
	}

	/**
	* Convert a Date to SQL including time
	*
	* @param date the DateTime to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_and_time_format.format(date) + "'";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value with time
	*/
	public String currentDateTimeSQLMethod() {
		return "CURRENT_TIMESTAMP()";
	}

	/**
	* Get the SQL command for top or first
	*
	* @param number The number of rows you would like back from the select.
	* @return Returns the SQL string command to limit the number of rows returned in a system
	* Note Mysql Can also retrieve the last amount,SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
	*/
	public String sqlFirstString(String sql, int number) {
		return sql;
	}

	/**
	* Converts a string value to a suitable value for an SQL statement
	*
	* @param source The value of the string
	* @return A converted string ready for use in SQL
	*/
	public String convertStringForSQL(String source) {
		return convertStringForSQLAsStatic(source);
	}

	private static String convertStringForSQLAsStatic(String source) {
		if (source == null) return null;
		return "'" + convertStringForSQLBeforeQuotes(source) + "'";
	}

	private static String convertStringForSQLBeforeQuotes(String source) {
		if (source == null) return null;
		return
		   source
		   .replaceAll("'","''")
		   .replaceAll("\\\\","\\\\\\\\");
	}

	/**
	* Converts a string after is has been returned from the database
	*
	* @param source The value of the string from the database
	* @return A converted string converted from the SQL syntax
	*/
	public String convertStringFromSQL(String source) {
		if (source == null) return null;
		return source;
	}

	public String dateSUB_HOUR(Date date,int hours) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+hours+" HOUR)";
	}

	public String dateSUB_MIN(Date date,int min) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+min+" MIN)";
	}

	public String dateSUB_SEC(Date date,int sec) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+sec+" SEC)";
	}
	
	public boolean mayUpdateFromResultSet(DBConnection dbcon)  throws SQLException{
		return true;
	}

	public void executeKeepAliveSQL(DBConnection dbcon)
	throws SQLException {
		Statement smt = dbcon.createStatement();
		smt.executeQuery("select 1 = 1");
		smt.close();
	}

	public void executeOptimizeTable(DBConnection connection,String table_name)
	throws SQLException {
		Statement smt = connection.createStatement();
		smt.executeQuery("optimize table " + table_name);
		smt.close();
	}

	public void dropIndex(DBConnection connection,String table_name,String index_name)
	throws SQLException,DataException {
		Statement smt = connection.createStatement();
		if (new VectorString(smt.executeQuery("show index from " + table_name),"Key_name").contains(index_name)) {
			Log.trace.out("DATABASE UPDATE INDEX","DROP INDEX ["+index_name+"] ON TABLE ["+table_name+"]");
			connection.execute("drop index " + index_name + " on " + table_name);
		}
		smt.close();
	}

	protected void createTable(DBConnection connection,String create_syntax,Entity entity,Field table_fields[])
	throws SQLException {
		String create_sql = create_syntax + " "+ entity.getTableName() + "( ";
		for (int i =0;i < table_fields.length;i++) {
			if (table_fields[i] instanceof FieldAutoId) {
				create_sql += table_fields[i].getName() + " bigint GENERATED ALWAYS AS IDENTITY  not null,primary key("+table_fields[i].getName()+"),";
			} else {
				create_sql += table_fields[i].getName() + " " + getSQLType(table_fields[i]) + ",";
			}
		}
		create_sql = create_sql.substring(0,create_sql.length()-1);
		create_sql += ")";
		Log.trace.out("Updating Database",create_sql);
		connection.execute(create_sql);
	}

	public String getSQLType(FieldAutoId field){return "bigint GENERATED ALWAYS AS IDENTITY ";}
	
	public String getSQLType(FieldBigDecimal field) {
		return "double";
	}
	public String getSQLType(FieldBigInteger field) {
		return "bigint";
	}
	public String getSQLType(FieldBoolean field) {
		return "char(1)";
	}
	public String getSQLType(FieldCharacter field) {
		return "char(1)";
	}
	public String getSQLType(FieldColor field) {
		return "char(6)";
	}
	public String getSQLType(FieldCurrency field) {
		return "decimal(10,2)";
	}
	public String getSQLType(FieldDate field) {
		return "date";
	}
	public String getSQLType(FieldDateTime field) {
		return "timestamp";
	}
	public String getSQLType(FieldDouble field) {
		return "double";
	}
	public String getSQLType(FieldInteger field) {
		return "int";
	}
	public String getSQLType(FieldLong field) {
		return "bigint";
	}
	public String getSQLType(FieldString field) {
		return "varchar("+field.getLength()+")";
	}
	public String getSQLType(FieldTime field) {
		return "time";
	}
	public String getSQLType(FieldText field) {
		return "long varchar(16384)";
	}
	public String getSQLType(FieldImage field) {
		return "blob";
	}
	public String getSQLType(FieldProperties field) {
		return "long varchar(1024)";
	}
	public String getSQLType(FieldBlob field) {
		return "blob";
	}

	public void addField(DBConnection dbcon,Field field)
	throws SQLException {
		if (!field.mayFieldBeNull()) {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null");
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null)");
		} else {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
		}
	}

	public void modifyTableField(DBConnection connection,String old_name,Field field)
	throws SQLException {
		if (!field.mayFieldBeNull()) {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field) +" not null");
			connection.execute("alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field) +" not null");
		} else {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field));
			connection.execute("alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field));
		}
	}

	public void modifyTableField(DBConnection connection,Field field)
	throws SQLException {
		modifyTableField(connection,field.getName(),field);
	}

	public String getDropField(String name,String tablename) {
		return "alter table " + tablename + " DROP COLUMN " + name;
	}

	public void setupDatabaseServer(DBConnection connect)
	throws SQLException {
		// do INIT
	}

	/**
	 * This will backup and zip the database
	 */
	public void backupDB(DBConnection dbcon,File dbfile)
	throws Throwable {
		throw new SQLException("Backup not implemented yet");
	}

	/**
	 * In order to do this the server must be stopped
	 */ 
	public void restoreDB(DBConnection dbcon,File dbfile)
	throws Throwable {
		throw new SQLException("Restore not implemented yet");
	}

	public boolean createDatabase(String path,String dbname,String username,String password)  {
		return true;
	}

	public void bulkLoadFile(DBConnection dbcon,String tablename,File file,String delimiter)
	throws SQLException {
		throw new SQLException("Database does not support bulk loading");
	}

	public void exportData(DBConnection dbcon,String select,File bulkfile)
	throws SQLException {
		throw new SQLException("Database does not support bulk exporting");
	}
}
